package dblayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;

import modellayer.Company;
import modellayer.KeyPerson;

/**
 * The KeyPersonsCompaniesDB class to exchange data betwixt SQL database and java
 * @author Allan Eriksen, Andreas Rye, Casper Madsen, Christian Stidsborg, Stefan Bols
 */

public class KeyPersonsCompaniesDB {
	private Connection con;
	/**
	 * @param con
	 */
	
	/**
	 * Constructor for the KeyPersonsCompaniesDB, also creates a connection to the SQL Database
	 */
	public KeyPersonsCompaniesDB() {
		con = DBConnection.getInstance().getDBcon();
	}
	
	/**
	 * This method searches for companies through keyPersonId
	 * @param keyPersonId
	 * @return <Company>miscWhere(wClause, "Company")
	 */
	public ArrayList<Company> getCompaniesFromKeyPerson(int keyPersonId) {
		String wClause = "keyPersonId="+keyPersonId;
		
		return this.<Company>miscWhere(wClause, "Company");
	}


	/**
	 * This method searches for companies through cvr
	 * @param cvr
	 * @return <KeyPerson>miscWhere(wClause, "KeyPerson")
	 */
	public ArrayList<KeyPerson> getKeyPersonFromCompany(int cvr) {
		String wClause = "cvr="+cvr;
		
		return this.<KeyPerson>miscWhere(wClause, "KeyPerson");
	}

	/**
	 * miscWhere method that returns an ArrayList with company and keyPerson objects
	 * @param wClause
	 * @param type
	 * @return list
	 */
	public <T> ArrayList<T> miscWhere(String wClause, String type) {
		ResultSet results;
		ArrayList<T> list = new ArrayList<T>();
		
		String query = buildQuery(wClause);
		
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			while ( results.next() ) {
				if (type=="KeyPerson") {
					list.add((T) buildKeyPerson(results));
				} else if (type=="Company") {
					list.add((T) buildCompany(results));
				}
			}
		}
		catch (Exception e) {
			System.out.println("SQL query: "+query);
			System.out.println("	error: "+e);
		}
		
		return list;
	}

	
	/**
	 * A method which uses wClause parameters to construct a query, in a format that the SQL database understands
	 * @param wClause
	 * @return query
	 */
	private String buildQuery(String wClause) {
		String query = "SELECT companyId, keyPersonId FROM sem2_KeyPersonsCompanies";
		
		if ( ! wClause.isEmpty() )
		{
			query += " WHERE "+wClause;
		}
		
		return query;
	}

	/**
	 * A method which extracts data from the database into Java
	 * @param results
	 * @return keyPersonObj
	 */
	private KeyPerson buildKeyPerson(ResultSet results) {
		KeyPerson keyPersonObj = null;
		
		try {
			keyPersonObj = new KeyPerson(results.getInt("keyPersonId"));
		}
		catch (SQLException e) {
			System.out.println("Error building KeyPerson object!\nError: " + e);
		}
		
		return keyPersonObj;
	}

	/**
	 * A method which extracts data from the database into Java
	 * @param results
	 * @return companyObj
	 */
	private Company buildCompany(ResultSet results) {
		Company companyObj = null;
		
		try {
			companyObj = new Company(results.getInt("companyId"));
		}
		catch (SQLException e) {
			System.out.println("Error building Company object!\nError: " + e);
		}
		
		return companyObj;
	}
}